﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public  class DAdMachine
    {    
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string  number)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tadmachine");
            strSql.Append(" where Number=@Number");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Number", MySqlDbType.VarChar)
             };
            parameters[0].Value = number;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.ADMachineInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tadmachine(");
            strSql.Append("Number,StationName,Telephone,PhoneNumber,Linkman,");
            strSql.Append("Address,InternetType,ExitPassword,Longitude,");
            strSql.Append("Latitude,XTRemoteID,ImageUrl)");
            strSql.Append(" values (");
            strSql.Append("@Number,@StationName,@Telephone,@PhoneNumber");
            strSql.Append(",@Linkman,@Address,@InternetType,@ExitPassword");
            strSql.Append(",@Longitude,@Latitude,@XTRemoteID,@ImageUrl)");            
            MySqlParameter[] parameters = {
					new MySqlParameter("@Number", MySqlDbType.VarChar,45),
					new MySqlParameter("@StationName", MySqlDbType.VarChar,60),
					new MySqlParameter("@Telephone", MySqlDbType.VarChar,45),
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,45),
                    	new MySqlParameter("@Linkman", MySqlDbType.VarChar,45),
					new MySqlParameter("@Address", MySqlDbType.VarChar,100),
					new MySqlParameter("@InternetType", MySqlDbType.VarChar,45),
					new MySqlParameter("@ExitPassword", MySqlDbType.Int32,10),
                    	new MySqlParameter("@Longitude", MySqlDbType.Double),
					new MySqlParameter("@Latitude", MySqlDbType.Double),
					new MySqlParameter("@XTRemoteID", MySqlDbType.VarChar,45),					
					new MySqlParameter("@ImageUrl", MySqlDbType.VarChar,45)};
            parameters[0].Value = model.Number;
            parameters[1].Value = model.StationName;
            parameters[2].Value = model.Telephone;
            parameters[3].Value = model.PhoneNumber;
            parameters[4].Value = model.Linkman;
            parameters[5].Value = model.Address;
            parameters[6].Value = model.InternetType;
            parameters[7].Value = model.ExitPassword;
            parameters[8].Value = model.Longitude;
            parameters[9].Value = model.Latitude;
            parameters[10].Value = model.XTRemoteId;
            parameters[11].Value = model.ImageUrl;
            
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.ADMachineInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tadmachine set ");
            //strSql.Append("Number=@Number,");
            strSql.Append("StationName=@StationName,");
            strSql.Append("Telephone=@Telephone,");
            strSql.Append("PhoneNumber=@PhoneNumber,");
            strSql.Append("Linkman=@Linkman,");
            strSql.Append("Address=@Address,");
            strSql.Append("InternetType=@InternetType,");
            strSql.Append("ExitPassword=@ExitPassword,");
            strSql.Append("Longitude=@Longitude,");
            strSql.Append("Latitude=@Latitude,");
            strSql.Append("XTRemoteID=@XTRemoteID,");
            strSql.Append("ImageUrl=@ImageUrl");
            strSql.Append(" where Number=@Number");
            MySqlParameter[] parameters = {
					new MySqlParameter("@StationName", MySqlDbType.VarChar,60),
					new MySqlParameter("@Telephone", MySqlDbType.VarChar,45),
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,45),
                    new MySqlParameter("@Linkman", MySqlDbType.VarChar,45),
					new MySqlParameter("@Address", MySqlDbType.VarChar,100),
					new MySqlParameter("@InternetType", MySqlDbType.VarChar,45),
					new MySqlParameter("@ExitPassword", MySqlDbType.Int32,10),
                    new MySqlParameter("@Longitude", MySqlDbType.Double),
					new MySqlParameter("@Latitude", MySqlDbType.Double),
					new MySqlParameter("@XTRemoteID", MySqlDbType.VarChar,45),					
					new MySqlParameter("@ImageUrl", MySqlDbType.VarChar,45),
					new MySqlParameter("@Number", MySqlDbType.VarChar)};
            
            parameters[0].Value = model.StationName;
            parameters[1].Value = model.Telephone;
            parameters[2].Value = model.PhoneNumber;
            parameters[3].Value = model.Linkman;
            parameters[4].Value = model.Address;
            parameters[5].Value = model.InternetType;
            parameters[6].Value = model.ExitPassword;
            parameters[7].Value = model.Longitude;
            parameters[8].Value = model.Latitude;
            parameters[9].Value = model.XTRemoteId;
            parameters[10].Value = model.ImageUrl;
            parameters[11].Value = model.Number;
            

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        public int UpdateUrlInList(string adUrl, string stationlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tadmachine set ");          
            strSql.Append("ImageUrl=@ImageUrl");
            strSql.Append(" where Number in (");
            strSql.Append(stationlist);
            strSql.Append(")");
            MySqlParameter[] parameters = {					 			
					new MySqlParameter("@ImageUrl", MySqlDbType.Text)};
            parameters[0].Value = adUrl;             
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            return rows;
        }



        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string number)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tadmachine ");
            strSql.Append(" where Number=@Number");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Number", MySqlDbType.VarChar)
             };
            parameters[0].Value = number;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tadmachine ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.ADMachineInfo GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tadmachine ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
            };
            parameters[0].Value = id;

            Model.ADMachineInfo model = new Model.ADMachineInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Number"] != null && ds.Tables[0].Rows[0]["Number"].ToString() != "")
                {
                    model.Number = ds.Tables[0].Rows[0]["Number"].ToString();
                }
                if (ds.Tables[0].Rows[0]["StationName"] != null && ds.Tables[0].Rows[0]["StationName"].ToString() != "")
                {
                    model.StationName = ds.Tables[0].Rows[0]["StationName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Telephone"] != null && ds.Tables[0].Rows[0]["Telephone"].ToString() != "")
                {
                    model.Telephone = ds.Tables[0].Rows[0]["Telephone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Linkman"] != null && ds.Tables[0].Rows[0]["Linkman"].ToString() != "")
                {
                    model.Linkman = ds.Tables[0].Rows[0]["Linkman"].ToString();
                }
                  if (ds.Tables[0].Rows[0]["Address"] != null && ds.Tables[0].Rows[0]["Address"].ToString() != "")
                {
                    model.Address = ds.Tables[0].Rows[0]["Address"].ToString();
                }
                  if (ds.Tables[0].Rows[0]["InternetType"] != null && ds.Tables[0].Rows[0]["InternetType"].ToString() != "")
                {
                    model.InternetType = ds.Tables[0].Rows[0]["InternetType"].ToString();
                }
                  if (ds.Tables[0].Rows[0]["ExitPassword"] != null && ds.Tables[0].Rows[0]["ExitPassword"].ToString() != "")
                {
                    model.ExitPassword = ds.Tables[0].Rows[0]["ExitPassword"].ToString();
                }
                  if (ds.Tables[0].Rows[0]["Longitude"] != null && ds.Tables[0].Rows[0]["Longitude"].ToString() != "")
                {
                    model.Longitude = double.Parse(ds.Tables[0].Rows[0]["Longitude"].ToString());
                }
                  if (ds.Tables[0].Rows[0]["Latitude"] != null && ds.Tables[0].Rows[0]["Latitude"].ToString() != "")
                {
                    model.Latitude = double.Parse(ds.Tables[0].Rows[0]["Latitude"].ToString());
                }
                  if (ds.Tables[0].Rows[0]["XTRemoteID"] != null && ds.Tables[0].Rows[0]["XTRemoteID"].ToString() != "")
                {
                    model.XTRemoteId = ds.Tables[0].Rows[0]["XTRemoteID"].ToString();
                }
                  if (ds.Tables[0].Rows[0]["ImageUrl"] != null && ds.Tables[0].Rows[0]["ImageUrl"].ToString() != "")
                {
                    model.ImageUrl = ds.Tables[0].Rows[0]["ImageUrl"].ToString();
                }
             
                return model;
            }
            else
            {
                return null;
            }
        }
        
      
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM tadmachine ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        public string GetAdUrlByNumber(string number)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ImageUrl ");
            strSql.Append(" FROM tadmachine where Number='");
            strSql.Append(number);
            strSql.Append("'");             
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds!=null && ds.Tables.Count>0 && ds.Tables[0].Rows.Count>0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }
         
    }
}
